/*-------------------<-- Start of Description-->---------------------\
| Write the variables from the input data set to an opened excel |
| spread sheet; |
|---------------------<-- End of Description-->----------------------|
|--------------------------------------------------------------------|
|------------<-- Start of Files or Arguments Needed-->---------------|
| Arguments: |
| indata = input dataset; |
| vars = create variable names for each column; |
| shtname = the name of the sheet you want to read; |
| Note: If variables are given, then the function will write the |
| variable names on the 1st row (in the order given accordingly),|
| and the values of the variables into the corresponding columns;|
| otherwise, the function will write all variables of the input |
| data set into the corresponding columns as they occur in the |
| dataset; |
| Note: if you want to arrange the order of the variables, then you |
| have to arrange them in the form of "vars=var format.|", but I|
| suggest you arrange the variables in the dataset, then apply |
| this function; |
|-------------<-- End of Files or Arguments Needed-->----------------|
|--------------------------------------------------------------------|
|------------------<-- Start of Files Created-->---------------------|
| Example: %excelwrite(indata=followup, vars=STUDY $10.|PT $10.| |
| CPEVENT $10.|VISIT |SUBEVE |ACTEVENT |QUALIFYV | |
| REPEATSN |PIDENT |INVSITE |INV |HOSP |CITY | |
| MDTNAME |VISDT |PHD |DEVTST) |
| Usage: %excelwrite(indata=,vars=,shtname=sheet1); |
\-------------------<-- End of Files Created-->---------------------*/
%macro excelwrite(indata=,vars=,shtname=sheet1);
/*--------------------------------------------\
| Copy Right: Duo Zhou; |
| Created: 2-5-2001 11:00pm; |
| Modified: 12-25-2001 4:40pm; |
| Purpose: Write the contents of a dataset to|
| an excel spread sheet; |
\--------------------------------------------*/
%local nvariables nobservations _i_ _j_ _k_;
%if &shtname eq %then %let shtname=sheet1;
%let dset=&indata; %let dsid=%sysfunc(open(&dset));
%if &dsid %then %do;
/*%put &vars;*/
%if (%qscan(&vars,1,%str( )) ne) %then %do;
/*%put 2nd if loop, current data set is &dsid;*/
%let nobservations=%sysfunc(attrn(&dsid,NOBS));
%let rc=%sysfunc(close(&dsid));
%let count=1;
%let var&count=%qscan(&vars, &count, %str(,()|));
%let varname&count=%qscan(&&var&count,1,%str(|,() ));
%do %while(%length(&&var&count) gt 0);
%let count=%eval(&count+1);
%let var&count=%qscan(&vars, &count, %str(,|()));
%let varname&count=%qscan(&&var&count,1,%str(|,() ));
%end;
%let nvariables =%eval(&count-1);
%let rows=%eval(&nobservations+1);
%let numoftabs=%eval(&nvariables-1);
filename excelsyl dde "excel|&shtname.!r1c1:r&rows.c&nvariables" notab LRECL=1048576;
data _null_;
file excelsyl;
set &indata;
if _n_=1 then do;
put %do _k_=1 %to &numoftabs; "&&varname&_k_" '09'x %end; "&&varname&nvariables";
end;
put %do _k_=1 %to &numoftabs; &&var&_k_ '09'x %end; &&var&nvariables;
run;
%end;
%else %do;
%let nvariables=%sysfunc(attrn(&dsid,NVARS)); %let nobservations=%sysfunc(attrn(&dsid,NOBS));
/*%put num of observation is &nobservations, num of variables is &nvariables;*/
%let rows=%eval(&nobservations+1); %let numoftabs=%eval(&nvariables-1);
%do _i_=1 %to &nvariables; %let var&_i_=%sysfunc(varname(&dsid,&_i_)); %end;
%let rc=%sysfunc(close(&dsid));
filename excelsyl dde "excel|&shtname.!r1c1:r&rows.c&nvariables" notab LRECL=1048576;
data _null_;
file excelsyl;
set &indata;
if _n_=1 then do;
put %do _k_=1 %to &numoftabs; "&&var&_k_" '09'x %end; "&&var&nvariables";
end;
put %do _k_=1 %to &numoftabs; &&var&_k_ '09'x %end; &&var&nvariables;
run;
%end;
%end;
%else %put ==> Alert! Open for data set "&dset" failed. ;
%mend excelwrite;